In [1]:
#Import library
import pandas as pd
import numpy as np
In [2]:
ls

'Ad Hoc Exercise Job#20-024.xlsx'     'Texas Higher Education-Copy2.ipynb'
 new.xlsx                             'Texas Higher Education.ipynb'
'Texas Higher Education-Copy1.ipynb'
In [3]:
#Reading excel sheet
df = pd.read_excel('Ad Hoc Exercise Job#20-024.xlsx', names='Sheet1',header=None, 
                   usecols=range(0,3), skiprows=range(0,4))
In [4]:
new_columns=['University','Fall 2017', 'Fall 2018']
df.columns = new_columns
In [5]:
#Print first 5 rows
df.head()
Out[5]:
University Fall 2017 Fall 2018
0 Angelo State University 10189.0 10242.0
1 Lamar University 13929.0 14176.0
2 Midwestern State University 5661.0 5712.0
3 Prairie View A&M University 9125.0 9516.0
4 Sam Houston State University 20938.0 21025.0
In [6]:
# Check data types
df.dtypes
Out[6]:
University     object
Fall 2017     float64
Fall 2018     float64
dtype: object
In [7]:
# select university data
df_university = df[0:37]
# df_university_total = df.loc[37:38,:]
In [9]:
# Calcualte the change rate from fall 2018
df_university[:]['Change Rate'] =(df_university[:]['Fall 2018']-df_university[:]['Fall 2017'])/df_university[:]['Fall 2017']
In [10]:
df_university.describe()
Out[10]:
Fall 2017 Fall 2018 Change Rate
count 37.000000 37.000000 37.000000
mean 17598.297297 17789.702703 -0.001091
std 15705.628713 16002.706635 0.045793
min 974.000000 890.000000 -0.169183
25% 7022.000000 6616.000000 -0.015333
50% 12236.000000 11929.000000 0.006895
75% 27642.000000 28489.000000 0.025013
max 62802.000000 63694.000000 0.070675
In [12]:
# Covert number of students in Fall 17 and Fall 18 to Int
df_university.loc[:,'Fall 2017']=df_university.loc[:,'Fall 2017'].astype(int)
df_university.loc[:,'Fall 2018']=df_university.loc[:,'Fall 2018'].astype(int)
In [13]:
df_university.dtypes
Out[13]:
University      object
Fall 2017        int64
Fall 2018        int64
Change Rate    float64
dtype: object
In [14]:
#sort value the by ascensding Change rate in percentage
df_university.sort_values(by=['Change Rate'],inplace=True)

/home/khanhp/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
In [15]:
# Reset index
df_university.reset_index(drop = True,inplace=True)
In [26]:
df_styled =df_university.style\
    .apply(lambda x: ['color: red' if x < 0 else "color: #228B22" for x in df_university['Change Rate']],
           axis =0,subset=['Change Rate'])\
    .background_gradient(cmap='YlGn',subset=['Fall 2017','Fall 2018'], )\
    .format({'Change Rate':"{:.2%}"})\
        .set_table_styles(
    [{'selector': 'tr:nth-of-type(odd)',
      'props': [('background', '#eee')]}, 
     {'selector': 'tr:nth-of-type(even)',
      'props': [('background', 'white')]},
     {'selector': 'th',
      'props': [('background', '#949292'), 
            ('color', 'white'),
            ('font-family', 'verdana')]},
     {'selector': 'td',
      'props': [('font-family', 'verdana')]},
    ]
    ).hide_index()
In [27]:
df_styled
Out[27]:
University Fall 2017 Fall 2018 Change Rate
The University of Texas Permian Basin 7022 5834 -16.92%
Texas A&M University at Galveston 1998 1806 -9.61%
Sul Ross State University Rio Grande College 974 890 -8.62%
Sul Ross State University 1996 1885 -5.56%
Texas Southern University 10237 9732 -4.93%
Texas A&M University-Central Texas 2575 2464 -4.31%
Texas A&M University-Commerce 12490 12072 -3.35%
Texas A&M University-Corpus Christi 12236 11929 -2.51%
The University of Texas at Tyler 9934 9716 -2.19%
Texas A&M University-Kingsville 8674 8541 -1.53%
West Texas A&M University 10060 10030 -0.30%
Texas State University 38666 38644 -0.06%
University of North Texas 38081 38087 0.02%
The University of Texas at El Paso 25020 25063 0.17%
Texas Woman's University 15321 15364 0.28%
Sam Houston State University 20938 21025 0.42%
The University of Texas at Austin 51425 51684 0.50%
Angelo State University 10189 10242 0.52%
University of Houston-Victoria 4351 4381 0.69%
Tarleton State University 13019 13118 0.76%
Midwestern State University 5661 5712 0.90%
Texas A&M University 62802 63694 1.42%
Texas A&M University-Texarkana 2038 2067 1.42%
Lamar University 13929 14176 1.77%
The University of Texas at Arlington 41712 42496 1.88%
University of Houston 45364 46324 2.12%
Texas A&M University-San Antonio 6460 6616 2.41%
University of Houston-Downtown 13913 14261 2.50%
The University of Texas Rio Grande Valley 27708 28489 2.82%
Texas A&M International University 7640 7884 3.19%
Texas Tech University 36634 37845 3.31%
Stephen F. Austin State University 12578 13058 3.82%
The University of Texas at Dallas 27642 28755 4.03%
Prairie View A&M University 9125 9516 4.28%
The University of Texas at San Antonio 30674 32101 4.65%
University of Houston-Clear Lake 8542 8961 4.91%
University of North Texas at Dallas 3509 3757 7.07%
In [23]:
# Export to Excel
df_styled.to_excel('Fall Semesters Comparation by Trend of Number of Student Enrollment.xlsx',sheet_name="University Enrollment", engine='openpyxl')